package dao.impl;

import common.model.Entity.UserRole;
import dao.UserDao;
import common.model.Entity.User;
import common.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.List;
import java.util.Optional;

/**
 * 用户数据访问实现类
 * 实现 UserDao 接口，提供 User 实体的数据库操作
 */
public class UserDaoImpl implements UserDao {

    private static final Logger logger = LoggerFactory.getLogger(UserDaoImpl.class);
    private final QueryRunner queryRunner = new QueryRunner();

    /**
     * 根据ID查询用户
     */
    @Override
    public Optional<User> findById(Long id) {
        String sql = """
                SELECT\s
                    u.id,
                    u.username,
                    u.password,
                    u.real_name AS realName,
                    u.email AS Email,
                    u.phone AS Phone,
                    u.gender AS Gender,
                    u.avatar AS Avatar,
                    u.status AS status,
                    r.role_number AS role
                FROM t_user u
                LEFT JOIN t_user_role ur ON u.id = ur.user_id
                LEFT JOIN t_role r ON ur.role_code = r.role_code
                WHERE u.id = ? AND u.is_deleted = 0;
                """;
        try (Connection conn = DBUtil.getConnection()) {
            User user = queryRunner.query(conn, sql, new BeanHandler<>(User.class), id);
            return Optional.ofNullable(user);
        } catch (SQLException e) {
            logger.error("根据ID查询用户失败: id={}", id, e);
            return Optional.empty();
        }
    }

    /**
     * 根据用户名查询用户
     */
    @Override
    public Optional<User> findByUsername(String username) {
        String sql = """
                SELECT\s
                    u.id,
                    u.username,
                    u.password,
                    u.real_name AS realName,
                    u.email AS Email,
                    u.phone AS Phone,
                    u.gender AS Gender,
                    u.avatar AS Avatar,
                    u.status AS status,
                    r.role_number AS role
                FROM t_user u
                LEFT JOIN t_user_role ur ON u.id = ur.user_id
                LEFT JOIN t_role r ON ur.role_code = r.role_code
                WHERE u.username = ? AND u.is_deleted = 0;
                """;
        try (Connection conn = DBUtil.getConnection()) {
            User user = queryRunner.query(conn, sql, new BeanHandler<>(User.class), username);
            return Optional.ofNullable(user);
        } catch (SQLException e) {
            logger.error("根据用户名查询用户失败: username={}", username, e);
            return Optional.empty();
        }
    }

    /**
     * 根据邮箱查询用户
     */
    @Override
    public Optional<User> findByEmail(String email) {
        String sql = "SELECT * FROM t_user WHERE email = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            User user = queryRunner.query(conn, sql, new BeanHandler<>(User.class), email);
            return Optional.ofNullable(user);
        } catch (SQLException e) {
            logger.error("根据邮箱查询用户失败: email={}", email, e);
            return Optional.empty();
        }
    }

    /**
     * 查询所有用户
     */
    @Override
    public List<User> findAll() {
        String sql = "SELECT " +
                "u.id, " +
                "u.username, " +
                "u.password, " +
                "u.real_name AS realName, " +
                "u.email AS Email, " +
                "u.phone AS Phone, " +
                "u.gender AS Gender, " +
                "u.avatar AS Avatar, " +
                "u.status AS status, " +
                "GROUP_CONCAT(r.role_number) AS role, " +
                "u.created_at " +
                "FROM t_user u " +
                "LEFT JOIN t_user_role ur ON u.id = ur.user_id " +
                "LEFT JOIN t_role r ON ur.role_code = r.role_code " +
                "WHERE u.is_deleted = 0 " +
                "GROUP BY u.id";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(User.class));
        } catch (SQLException e) {
            logger.error("查询所有用户失败", e);
            return List.of();
        }
    }

    /**
     * 保存用户
     */
    @Override
    public boolean save(User user) {
        String insertUserSql = "INSERT INTO t_user " +
                "(username, password, real_name, email, phone, gender, avatar, status,is_deleted,created_at) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
        String insertUserRoleSql = "INSERT INTO t_user_role (user_id, role_code) VALUES (?, ?)";
        try (Connection conn = DBUtil.getConnection()) {
            conn.setAutoCommit(false);

            try (PreparedStatement ps = conn.prepareStatement(insertUserSql, Statement.RETURN_GENERATED_KEYS)) {
                setUserSql(user, ps);

                int affectedRows = ps.executeUpdate();
                if (affectedRows == 0) {
                    conn.rollback();
                    throw new SQLException("创建用户失败，未插入任何行。");
                }

                // 获取生成的 user_id
                Long userId;
                try (ResultSet rs = ps.getGeneratedKeys()) {
                    if (rs.next()) {
                        userId = rs.getLong(1);
                        user.setId(userId);
                    } else {
                        conn.rollback();
                        throw new SQLException("创建用户失败，未获取到 ID。");
                    }
                }

                // 插入角色
                try (PreparedStatement psRole = conn.prepareStatement(insertUserRoleSql)) {
                    UserRole role = UserRole.fromCode(user.getRole());
                    psRole.setLong(1, userId);
                    psRole.setString(2, role.getDbCode());
                    psRole.executeUpdate();
                }
            }
            conn.commit();
            return true;
        } catch (SQLException e) {
            logger.error("保存用户失败: {}", user, e);
            return false;
        }
    }

    /**
     * 更新用户
     */
    @Override
    public boolean update(User user) {
        String updateUserSql = "UPDATE t_user SET " +
                "username = ?, " +
                "password = ?, " +
                "real_name = ?, " +
                "email = ?, " +
                "phone = ?, " +
                "gender = ?, " +
                "avatar = ?, " +
                "status = ?, " +
                "is_deleted= ?,"+
                "updated_at = NOW() " +
                "WHERE id = ? AND is_deleted = 0";

        String deleteUserRoleSql = "DELETE FROM t_user_role WHERE user_id = ?";
        String insertUserRoleSql = "INSERT INTO t_user_role (user_id, role_code) VALUES (?, ?)";

        try (Connection conn = DBUtil.getConnection()) {
            conn.setAutoCommit(false);

            // 更新用户基本信息
            try (PreparedStatement ps = conn.prepareStatement(updateUserSql)) {
                setUserSql(user, ps);
                ps.setLong(10, user.getId());

                int rows = ps.executeUpdate();
                if (rows == 0) {
                    conn.rollback();
                    throw new SQLException("更新用户失败，未找到用户ID=" + user.getId());
                }
            }

            // 删除旧角色
            try (PreparedStatement ps = conn.prepareStatement(deleteUserRoleSql)) {
                ps.setLong(1, user.getId());
                ps.executeUpdate();
            }

            // 插入新角色
            try (PreparedStatement ps = conn.prepareStatement(insertUserRoleSql)) {
                UserRole role = UserRole.fromCode(user.getRole());
                ps.setLong(1, user.getId());
                ps.setString(2, role.getDbCode());
                ps.executeUpdate();
            }

            conn.commit();
            return true;
        } catch (SQLException e) {
            logger.error("更新用户失败: {}", user, e);
            return false;
        }
    }

    /**
     * 设置 PreparedStatement 参数
     */
    private void setUserSql(User user, PreparedStatement ps) throws SQLException {
        ps.setString(1, user.getUsername());
        ps.setString(2, user.getPassword());
        ps.setString(3, user.getRealName());
        ps.setString(4, user.getEmail());
        ps.setString(5, user.getPhone());
        ps.setObject(6, user.getGender());
        ps.setString(7, user.getAvatar());
        ps.setInt(8, user.getStatus());
        ps.setInt(9,user.getIsDeleted());
    }

    /**
     * 逻辑删除用户
     */
    @Override
    public boolean deleteById(Long id) {
        String sql = "UPDATE t_user SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql, id);
            return affected > 0;
        } catch (SQLException e) {
            logger.error("删除用户失败: id={}", id, e);
            return false;
        }
    }

    /**
     * 检查用户名是否存在
     */
    @Override
    public boolean existsByUsername(String username) {
        String sql = "SELECT COUNT(*) FROM t_user WHERE username = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            Long count = queryRunner.query(conn, sql, new ScalarHandler<>(), username);
            return count != null && count > 0;
        } catch (SQLException e) {
            logger.error("检查用户名是否存在失败: username={}", username, e);
            return false;
        }
    }

    /**
     * 检查邮箱是否存在
     */
    @Override
    public boolean existsByEmail(String email){
        String sql="SELECT COUNT(*) FROM t_user WHERE email = ? AND is_deleted=0";
        try(Connection conn =DBUtil.getConnection()){
            Long count=queryRunner.query(conn,sql,new ScalarHandler<>(),email);
            return count!=null && count>0;
        }catch (SQLException e){
            logger.error("检查用户邮箱是否存在失败: email={}", email, e);
            return false;
        }
    }

    /**
     * 统计用户数量
     */
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM t_user WHERE is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new ScalarHandler<>());
        } catch (SQLException e) {
            logger.error("统计用户数量失败", e);
            return 0;
        }
    }

    /**
     * 更新用户登录信息
     */
    @Override
    public boolean updateLoginInfo(Long userId, String loginIp) {
        String sql = "UPDATE t_user SET last_login_time = CURRENT_TIMESTAMP, last_login_ip = ? WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, loginIp);
            ps.setLong(2, userId);

            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            logger.error("修改用户登录信息失败: userId={}", userId, e);
            return false;
        }
    }

    /**
     * 更新用户密码
     */
    @Override
    public boolean updatePassword(Long userId, String newPassword) {
        String sql = "UPDATE t_user SET password = ?, updated_at = NOW() " +
                "WHERE id = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, newPassword);
            ps.setLong(2, userId);

            int rows = ps.executeUpdate();
            return rows > 0;
        } catch (SQLException e) {
            logger.error("修改用户密码失败: userId={}", userId, e);
            return false;
        }
    }
}
